﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Linq;
using CNative.Utilities;
using System.Data.Common;
using Dapper;

namespace CNative.Dapper.Utils
{
    public abstract class BaseProvider
    {
        #region BaseProvider
        public BaseProvider(IDbHelper _db)
        {
            dbHelper = _db;
            _dbProviderFactory = new Lazy<DbProviderFactory>(CreateDbProviderFactory, true);
        }
        #endregion

        #region 关键字
        /// <summary>
        /// 数据库提供程序名字
        /// </summary>
        public virtual string ProviderName
        {
            get
            {
                //return "System.Data.OracleClient";//依赖于oracle官方驱动，需要另外安装oracle客户端
                //return "Oracle.DataAccess.Client";//Oracle数据库，官方非托管驱动，限制比较多
                return "Oracle.ManagedDataAccess.Client";//Oracle官方托管驱动,10g以下版本不支持，无任何依赖
            }
        }
        /// <summary>
        /// 数据库提供程序名字
        /// </summary>
        public virtual string ProviderNameFactory
        {
            get
            {
                return ProviderName;
            }
        }
        protected DbConnection Connection
        {
            get
            {
                var connection = _dbProviderFactory.Value.CreateConnection();
                connection.ConnectionString = dbHelper.ConnectString;
                return connection;
            }
        }
        protected Lazy<DbProviderFactory> _dbProviderFactory { get; }

        /// <summary>
        /// 获取数据源类的提供者实例
        /// </summary>
        /// <param name="dbType">数据库类型</param>
        /// <returns></returns>
        public virtual System.Data.Common.DbProviderFactory DbProviderFactory
        {
            get
            {
                return _dbProviderFactory.Value;
            }
        }

        public readonly IDbHelper dbHelper = null;

        /// <summary>
        /// 数据库类型
        /// </summary>
        public virtual DatabaseType DBType { get { return DatabaseType.SqlServer; } }
        /// <summary>
        /// 参数关键字 @ or :
        /// </summary>
        public virtual string ParamKeyword
        {
            get
            {
                return "@";
            }
        }
        /// <summary>
        /// 关键字前缀 [
        /// </summary>
        public virtual string SuffixLeft
        {
            get
            {
                return "[";
            }
        }
        /// <summary>
        /// 关键字后缀 ]
        /// </summary>
        public virtual string SuffixRigh
        {
            get
            {
                return "]";
            }
        }
        public virtual IDbFirst DbFirst { get { return new DbFirstProvider(this.dbHelper.DBName); } }
        public virtual ICodeFirst CodeFirst
        {
            get
            {
                var codeFirst = new CodeFirstProvider(this.dbHelper.DBName);
                codeFirst.Init(Connection.Database);
                return codeFirst;
            }
        }

        public virtual string FormatByQuote(string fieldName)
        {
            return GetTranslationColumnName(fieldName);// SuffixLeft + fieldName + SuffixRigh;
        }
        public virtual string GetTranslationTableName(string name)
        {
            Check.ArgumentNullException(name, string.Format(ErrorMessage.ObjNotExist, "Table Name"));
            if (!name.Contains("<>f__AnonymousType") && name.IsContainsIn("(", ")", SuffixLeft) && name != "Dictionary`2")
            {
                return name;
            }
            if (name.IsContainsIn("(", ")", SuffixLeft))
            {
                return name;
            }
            if (name.Contains("."))
            {
                return string.Join(".", name.Split('.').Select(it => SuffixLeft + it + SuffixRigh));
            }
            else
            {
                return SuffixLeft + name + SuffixRigh;
            }
        }
        public virtual string GetTranslationColumnName(string propertyName)
        {
            if (propertyName.Contains(SuffixLeft)) return propertyName;
            if (propertyName.Contains("."))
            {
                return string.Join(".", propertyName.Split('.').Select(it => SuffixLeft + it + SuffixRigh));
            }
            else
                return SuffixLeft + propertyName + SuffixRigh;
        }

        public virtual string GetNoTranslationColumnName(string name)
        {
            if (name.Contains("="))
            {
                name = name.Split('=').First();
            }
            if (!name.Contains(SuffixLeft)) return name;
            return name == null ? string.Empty : System.Text.RegularExpressions.Regex.Match(name, @".*" + "\\" + SuffixLeft + "(.*?)" + "\\" + SuffixRigh + "").Groups[1].Value;
        }
        public virtual List<DbColumnInfo> GetColumnInfosByTableName(string tableName, bool isCache = true)
        {
            if (string.IsNullOrEmpty(tableName)) return new List<DbColumnInfo>();
            return GetColumnInfosByTableName(tableName, Connection.Database, isCache);
        }
        public virtual List<DbColumnInfo> GetColumnInfosByTableName(string tableName, string dbName, bool isCache = true)
        {
            if (string.IsNullOrEmpty(tableName)) return new List<DbColumnInfo>();
            return Funs.GetDbDbColumnInfo(dbHelper, tableName, dbName);
        }
        public virtual List<DbTableInfo> GetTableInfoList(string dbName, bool isCache = true)
        {
            string cacheKey = "BaseProvider.GetTableInfoList";
            cacheKey = GetCacheKey(cacheKey);
            //var dbName = Connection.Database;
            var result = new List<DbTableInfo>();
            #region Oracle/Sqlite/MsAccess
            if (DBType == DatabaseType.Oracle)
            {
                if (dbName.IsNullOrEmpty())
                    dbName = "SYS";
                dbName = dbName.ToUpper();
            }
            else if (DBType == DatabaseType.Sqlite)
            {
                dbName = "";
            }
            else if (DBType == DatabaseType.MsAccess)
            {
                var lis = Funs.GetTableNameList(this.dbHelper, "TABLE");
                lis?.ForEach(f => { var tb = new DbTableInfo() { TableName = f }; result.Add(tb); });
                return result;
            }
            #endregion
            if (isCache)
                result = GetListOrCache<DbTableInfo>(cacheKey, string.Format(GetTableInfoListSql, dbName));
            else
            {
                var sqle = this.dbHelper.CreateSqlEntity(string.Format(GetTableInfoListSql, dbName));
                result = this.dbHelper.Query<DbTableInfo>(sqle);
                //var dt = this.dbHelper.QueryDataTable(sqle);
                //if (dt != null && dt.Rows.Count > 0)
                //{
                //    for (int i = 0; i < dt.Rows.Count; i++)
                //    {
                //        var tb = new DbTableInfo();
                //        tb.TableName = dt.Rows[i].GetColumnValue("name").NullToStr();
                //        tb.Description = dt.Rows[i].GetColumnValue("Description").NullToStr();
                //        result.Add(tb);
                //    }
                //}
            }
            foreach (var item in result)
            {
                item.DbObjectType = DbObjectType.Table;
            }
            return result;
        }
        public virtual List<DbTableInfo> GetViewInfoList(string dbName, bool isCache = true)
        {
            string cacheKey = "BaseProvider.GetViewInfoList";
            cacheKey = GetCacheKey(cacheKey);
            //var dbName = Connection.Database;
            var result = new List<DbTableInfo>();
            #region Oracle/Sqlite/MsAccess
            if (DBType == DatabaseType.Oracle)
            {
                if (dbName.IsNullOrEmpty())
                    dbName = "SYS";
                dbName = dbName.ToUpper();
            }
            else if (DBType == DatabaseType.Sqlite)
            {
                dbName = "";
            }
            else if (DBType == DatabaseType.MsAccess)
            {
                var lis = Funs.GetTableNameList(this.dbHelper, "VIEW");
                lis?.ForEach(f => { var tb = new DbTableInfo() { TableName = f }; result.Add(tb); });
                return result;
            }
            #endregion
            if (isCache)
                result = GetListOrCache<DbTableInfo>(cacheKey, string.Format(GetViewInfoListSql, dbName));
            else
            {
                var sqle = this.dbHelper.CreateSqlEntity(string.Format(GetViewInfoListSql, dbName));
                result = this.dbHelper.Query<DbTableInfo>(sqle);
            }
            foreach (var item in result)
            {
                item.DbObjectType = DbObjectType.View;
            }
            return result;
        }
        /// <summary>
        /// 判断是否为字符串类别。
        /// </summary>
        /// <param name="dbType"></param>
        /// <returns></returns>
        protected virtual bool IsStringType(DbType dbType)
        {
            return dbType == DbType.AnsiString || dbType == DbType.AnsiStringFixedLength || dbType == DbType.String || dbType == DbType.StringFixedLength;
        }
        public virtual void CheckConnection()
        {
            if (this.Connection.State != ConnectionState.Open)
            {
                try
                {
                    this.Connection.Open();
                }
                catch (Exception ex)
                {
                    Check.Exception(true, ErrorMessage.ConnnectionOpen, ex.Message);
                }
            }
        }
        #endregion

        #region DbProviderFactory
        protected virtual DbProviderFactory CreateDbProviderFactory()
        {
            DbProviderFactory dbProvider = null;
            Action actionIfNull = () =>
            {
                var asy = InvokeLocalHelper.AssemblyLoad(ProviderName);
                if (asy == null)
                {
                    throw new Exception("CreateDbProviderFactory（）【" + ProviderName + ".dll】未能加载");
                }
                try
                {
                    dbProvider = DbProviderFactories.GetFactory(ProviderName);
                }
                catch { }
                if (dbProvider == null)
                {
                    try
                    {
                        dbProvider = asy.CreateInstance(ProviderNameFactory) as DbProviderFactory;
                    }
                    catch { dbProvider = FastReflection.FastInstance<DbProviderFactory>(ProviderNameFactory); }
                }
            };
            try
            {
                //var dt = DbProviderFactories.GetFactoryClasses();
                dbProvider = DbProviderFactories.GetFactory(ProviderName);
                if (dbProvider == null)
                    actionIfNull();
            }
            catch (Exception ex)
            {
                _ = ex;
                try
                {
                    actionIfNull();
                }
                catch (Exception ex2)
                {
                    throw ex2;
                }
            }
            if (dbProvider == null)
            {
                throw new Exception("CreateDbProviderFactory() " + ProviderName + ".dll 未能加载");
            }
            return dbProvider;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public virtual IDbConnection CreateConnection()
        {
            return _dbProviderFactory.Value.CreateConnection();
        }
        /// <summary>
        /// test.dbo.tablename
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbHelper"></param>
        /// <param name="tableType"></param>
        /// <returns></returns>
        public virtual string GetSchemaTableName(Type tableType)
        {
            var tb = Funs.GetDbTableInfo(dbHelper, tableType);
            if (tb == null)
            {
                return "";
            }
            return tb.Schema.IsNullOrEmpty() ? SuffixLeft + tb.TableName + SuffixRigh : SuffixLeft + tb.Schema + SuffixRigh + ".dbo." + SuffixLeft + tb.TableName + SuffixRigh;
        }
        #endregion

        #region CreateSqlEntity
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public virtual SqlEntity CreateSqlEntity(IDbHelper dbHelper)
        {
            return new SqlEntity(dbHelper);
        }
        #endregion

        #region GetDbParameter
        /// <summary>
        /// 添加参数
        /// </summary>
        /// <param name="parameters"></param>
        public void AddParameters(SqlEntity sqlent, params CNDbParameter[] dbParameters)
        {
            sqlent?.AddParameters(dbParameters);
        }
        /// <summary>
        /// 添加参数
        /// </summary>
        /// <param name="propName">参数名称</param>
        /// <param name="val">参数值</param>
        /// <param name="sqlent"></param>
        /// <param name="parameterName"></param>
        /// <param name="tb"></param>
        /// <param name="paramSuffix">参数前缀</param>
        public virtual bool AddDbParameter(IDbHelper dbHelper, string propName, object val, SqlEntity sqlent, out string parameterName, string paramSuffix = "wp_", DbTableInfo tb = null)
        {
            parameterName = "";
            if (sqlent == null) return false;
            paramSuffix = paramSuffix.NullToStr() + (sqlent.Parameters == null ? 0 : sqlent.Parameters.ParameterNames.Count()) + "_";

            var para = GetDbParameter(propName, val, paramSuffix, tb);
            if (para != null)
            {
                if (sqlent.Parameters == null)
                    sqlent.Parameters = new DynamicParameters();

                parameterName = para.ParameterName;
                //sqlent.Parameters[parameterName] = para;
                sqlent.Parameters.Add(para.ParameterName, para.Value, para.DbType, para.Direction, para.Size, para.Precision, para.Scale);

                return true;
            }
            return false;
        }

        /// <summary>
        /// 获取DbParameter
        /// </summary>
        /// <param name="dbType">数据库类型</param>
        /// <returns></returns>
        public virtual CNDbParameter GetDbParameter(string propName, object val, string paramSuffix = "exp_",
            DbTableInfo tb = null, ParameterDirection direction = ParameterDirection.Input, DbType? valDBType = null)
        {
            CNDbParameter para = null;
            //--------------------------------------------------------------------------------------------------------------
            var frets = FillerParameter(propName, val, paramSuffix);
            if (frets.Item1)
            {
                return para;
            }
            var parameterName = frets.Item2;
            val = frets.Item3;
            //--------------------------------------------------------------------------------------------------------------
            #region CNDbParameter
            para = new CNDbParameter(parameterName, val)
            {
                Direction = direction,
                //DbType = valDBType
            };
            if (valDBType != null)
                para.DbType = valDBType;

            if (tb != null && tb.Columns != null)
            {
                var dtinfo = tb.Columns.Find(f => f.Name.Equals(propName.Trim(), StringComparison.OrdinalIgnoreCase));
                if (dtinfo != null)
                {
                    try
                    {
                        if (dtinfo.MaxLength > 0 && dtinfo.MaxLength < int.MaxValue)
                            (para as CNDbParameter).Size = (int)dtinfo.MaxLength;
                    }
                    catch { }
                }
            }
            #endregion
            return para;
        }
        /// <summary>
        /// 入参处理
        /// </summary>
        /// <param name="propName"></param>
        /// <param name="val"></param>
        /// <param name="paramSuffix"></param>
        /// <returns></returns>
        protected virtual Tuple<bool, string, object> FillerParameter(string propName, object val, string paramSuffix = "exp_")
        {
            var parameterName = ParamKeyword + (paramSuffix + propName).TrimStart(ParamKeyword.ToCharArray());
            val = val ?? DBNull.Value;
            if (val?.Equals(DateTime.MinValue) == true) val = new DateTime(1970, 1, 1);
            val = ObjToDataTable(val);
            //--------------------------------------------------------------------------------------------------------------
            if (((val.IsNullOrEmpty() || (val is DateTime dat && dat <= DateTime.MinValue)) &&
                    (paramSuffix.StartsWith("valp_") || paramSuffix.StartsWith("insp_")) &&
                     (propName.ToLower() == "createdate" || propName.ToLower() == "updatedate" ||
                      propName.ToLower() == "createtime" || propName.ToLower() == "updatetime"))
                    || ((val.IsNullOrEmpty() || (val is DateTime dat2 && dat2 <= DateTime.MinValue)) &&
                        paramSuffix.StartsWith("setp_") &&
                        (propName.ToLower() == "updatedate" || propName.ToLower() == "updatetime")))
            {
                return Tuple.Create(true, parameterName, val);
            }
            return Tuple.Create(false, parameterName, val);
        }

        /// <summary>
        /// 入参值处理
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        protected virtual object ObjToDataTable(object obj)
        {
            if (obj.IsNullOrDBNull_() || obj is DataTable)
                return obj;
            if (obj is DateTime dat && dat <= DateTime.MinValue)
            {
                return DateTime.Now;
            }
            //if (obj is Array || obj is System.Collections.IList)
            //{
            //    var listobj = (obj as System.Collections.IEnumerable).Cast<object>().Select(x => x).ToList();
            //    if (listobj != null && listobj.Count > 0)
            //    {
            //        var str = string.Join(",", listobj);
            //        return str;
            //        //var tb = new System.Data.DataTable();
            //        //tb.Columns.Add("Id", listobj[0].GetType());
            //        //listobj.ForEach(val =>
            //        //{
            //        //    var row = tb.NewRow();
            //        //    row[0] = val;
            //        //    tb.Rows.Add(row);
            //        //});
            //        //return tb;
            //    }
            //}
            return obj;
        }
        #endregion

        #region MappingTypes
        /// <summary>
        /// 类型映射
        /// sqlTypeName，CsharpType，ParameterType
        /// </summary>
        public abstract List<Tuple<string, string, string>> MappingTypes { get; }
        public Tuple<string, string, string> AddMappingType(string sqlTypeName, string typeName, string parameterType)
        {
            var oldret = MappingTypes?.Find(f => f.Item1 == sqlTypeName);
            if (oldret != null) return oldret;
            var ret = Tuple.Create(sqlTypeName, typeName, parameterType);
            MappingTypes.Add(ret);
            return ret;
        }
        /// <summary>
        /// 获取库类型
        /// </summary>
        /// <param name="csharpTypeName"></param>
        /// <returns></returns>
        public virtual string GetDbTypeName(string csharpTypeName)
        {
            if (csharpTypeName == Constants.ByteArrayType.Name)
                return "varbinary";
            csharpTypeName = CheckCsharpTypeName(csharpTypeName);
            var mappings = this.MappingTypes?.Where(it => it.Item2.Equals(csharpTypeName, StringComparison.CurrentCultureIgnoreCase)).ToList();
            if (mappings != null && mappings.Count > 0)
                return mappings.First().Item1;
            else
                return "varchar";
        }
        protected virtual string CheckCsharpTypeName(string csharpTypeName)
        {
            if (csharpTypeName.ToLower() == "int32")
                csharpTypeName = "int";
            else if (csharpTypeName.ToLower() == "int16")
                csharpTypeName = "short";
            else if (csharpTypeName.ToLower() == "int64")
                csharpTypeName = "long";
            else if (csharpTypeName.ToLower().In("boolean", "bool"))
                csharpTypeName = "bool";

            return csharpTypeName;
        }
        /// <summary>
        /// 获取Csharp类型
        /// </summary>
        /// <param name="dbTypeName"></param>
        /// <returns></returns>
        public virtual string GetCsharpTypeName(string dbTypeName)
        {
            var mappings = this.MappingTypes?.Where(it => it.Item1.Equals(dbTypeName, StringComparison.CurrentCultureIgnoreCase));
            return !mappings.IsNullOrEmpty_() ? mappings.First().Item2 : "string";
        }
        /// <summary>
        /// 获取sql Parameter Type
        /// </summary>
        /// <param name="dbTypeName"></param>
        /// <returns></returns>
        public virtual string GetParameterTypeName(string dbTypeName)
        {
            var mappings = this.MappingTypes?.Where(it => it.Item1.Equals(dbTypeName, StringComparison.CurrentCultureIgnoreCase));
            return !mappings.IsNullOrEmpty_() ? mappings.First().Item3 : "SqlDbType.VarChar";
        }
        public virtual string GetConvertString(string dbTypeName)
        {
            string result = string.Empty;
            switch (dbTypeName.ToLower())
            {
                #region Int
                case "int":
                    result = "Convert.ToInt32";
                    break;
                #endregion

                #region String
                case "nchar":
                case "char":
                case "ntext":
                case "nvarchar":
                case "varchar":
                case "text":
                    result = "Convert.ToString";
                    break;
                #endregion

                #region Long
                case "bigint":
                    result = "Convert.ToInt64";
                    break;
                #endregion

                #region Bool
                case "bit":
                    result = "Convert.ToBoolean";
                    break;

                #endregion

                #region Datetime
                case "timestamp":
                case "smalldatetime":
                case "datetime":
                case "date":
                case "datetime2":
                    result = "Convert.ToDateTime";
                    break;
                #endregion

                #region Decimal
                case "smallmoney":
                case "single":
                case "numeric":
                case "money":
                case "decimal":
                    result = "Convert.ToDecimal";
                    break;
                #endregion

                #region Double
                case "float":
                    result = "Convert.ToDouble";
                    break;
                #endregion

                #region Byte[]
                case "varbinary":
                case "binary":
                case "image":
                    result = "byte[]";
                    break;
                #endregion

                #region Float
                case "real":
                    result = "Convert.ToSingle";
                    break;
                #endregion

                #region Short
                case "smallint":
                    result = "Convert.ToInt16";
                    break;
                #endregion

                #region Byte
                case "tinyint":
                    result = "Convert.ToByte";
                    break;

                #endregion

                #region Guid
                case "uniqueidentifier":
                    result = "Guid.Parse";
                    break;
                #endregion

                #region Null
                default:
                    result = null;
                    break;
                    #endregion
            }
            return result;
        }
        #endregion

        #region Sql Fun Templet
        #region 聚合函数
        /// <summary>
        /// SQL AVG() 函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="column_name">列名</param>
        /// <returns>函数返回指定列的值的平均值</returns>
        public virtual string SQL_AVG(string column_name)
        {
            return $"AVG({column_name})";
        }
        /// <summary>
        /// SQL COUNT() 函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="column_name">列名</param>
        /// <returns>函数返回指定列值的数目</returns>
        public virtual string SQL_COUNT(string column_name)
        {
            return $"COUNT({(column_name.IsNullOrEmpty() ? "0" : column_name)})";
        }
        /// <summary>
        /// SQL MAX() 函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="column_name">列名</param>
        /// <returns>函数返回指定列的最大值</returns>
        public virtual string SQL_MAX(string column_name)
        {
            return $"MAX({column_name})";
        }
        /// <summary>
        /// SQL MIN() 函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="column_name">列名</param>
        /// <returns>函数返回指定列的最小值</returns>
        public virtual string SQL_MIN(string column_name)
        {
            return $"MIN({column_name})";
        }
        /// <summary>
        /// SQL SUM() 函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="column_name">列名</param>
        /// <returns>函数返回指定列的总数</returns>
        public virtual string SQL_SUM(string column_name)
        {
            return $"SUM({column_name})";
        }
        #endregion

        /// <summary>
        /// SQL NVL() 从两个表达式返回一个非 null 值	函数
        /// </summary>
        /// <param name="check_expression">将被检查是否为 NULL的表达式。可以是任何类型的。</param>
        /// <param name="replacement_value">在 check_expression 为 NULL时将返回的表达式。replacement_value 必须与 check_expresssion 具有相同的类型。</param>
        /// <returns>如果 check_expression 不为 NULL，那么返回该表达式的值；否则返回 replacement_value</returns>
        public virtual string SQL_NVL(string check_expression, string replacement_value)
        {
            return $"ISNULL({check_expression},{replacement_value})";
        }

        /// <summary>
        /// SQL NOW() 函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="column_name">列名</param>
        /// <returns>NOW() 函数返回当前系统的日期和时间</returns>
        public virtual string SQL_NOW()
        {
            return SqlDateNow;
        }

        /// <summary>
        /// SQL UCASE() 函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="column_name">列名</param>
        /// <returns>函数把字段的值转换为大写</returns>
        public virtual string SQL_UCASE(string column_name)
        {
            return $"UPPER({column_name})";
        }

        /// <summary>
        /// SQL LCASE() 函数
        /// </summary>
        /// <param name="column_name">列名</param>
        /// <returns>函数把字段的值转换为小写</returns>
        public virtual string SQL_LCASE(string column_name)
        {
            return $"LOWER({column_name})";
        }

        /// <summary>
        /// SQL LEFT(s,n) 函数
        /// </summary>
        /// <param name="str">列名</param>
        /// <returns>返回字符串 str 的前 length 个字符</returns>
        public virtual string SQL_LEFT(string str, int length)
        {
            return $"LEFT({str},{length})";
        }
        /// <summary>
        /// SQL RIGHT(s,n) 函数
        /// </summary>
        /// <param name="str">列名</param>
        /// <returns>返回字符串 str 的前 length 个字符</returns>
        public virtual string SQL_RIGHT(string str, int length)
        {
            return $"RIGHT({str},{length})";
        }
        /// <summary>
        /// SQL SUBSTR(s, start, length) 函数
        /// </summary>
        /// <param name="str">列名</param>
        /// <returns>从字符串 str 的 start 位置截取长度为 length 的子字符串</returns>
        public virtual string SQL_SUBSTR(string str, int start, int length)
        {
            return $"SUBSTRING({str},{1 + start},{length})";
        }
        /// <summary>
        /// SQL TRIM(s) 函数
        /// </summary>
        /// <param name="str">列名</param>
        /// <returns>去掉字符串 str 开始和结尾处的空格</returns>
        public virtual string SQL_TRIM(string str)
        {
            return $"TRIM({str})";
        }
        /// <summary>
        /// SQL REPLACE(s,s1,s2) 函数
        /// </summary>
        /// <param name="s">列名</param>
        /// <returns>将字符串 s2 替代字符串 s 中的字符串 s1</returns>
        public virtual string SQL_REPLACE(string s, string s1, string s2)
        {
            return $"REPLACE({s},{s1},{s2})";
        }

        /// <summary>
        /// SQL CAST() 转换数据类型	函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="column_name">列名</param>
        /// <param name="type">数据类型</param>
        /// <returns>转换数据类型</returns>
        public virtual string SQL_CAST(string column_name, string type)
        {
            return $"CAST({column_name} AS {type})";
        }

        public virtual string SQL_ToInt32(string column_name)
        {
            return SQL_CAST(column_name, "INT");
        }

        public virtual string SQL_ToInt64(string column_name)
        {
            return SQL_CAST(column_name, "BIGINT");
        }
        public virtual string SQL_ToDouble(string column_name)
        {
            return SQL_CAST(column_name, "FLOAT");
        }
        /// <summary>转换高精度的十进制数（一般用于货币）</summary>
        /// <param name="column_name"></param>
        /// <param name="d">小数位数</param>
        /// <returns></returns>
        public virtual string SQL_ToDecimal(string column_name, int d)
        {
            if (d < 0 || d > 10) d = 4;
            return SQL_CAST(column_name, "DECIMAL(18, " + d + ")");
        }

        public virtual string SQL_ToBool(string column_name)
        {
            return SQL_CAST(column_name, "BIT");
        }

        public virtual string SQL_ToVarchar(string column_name)
        {
            return SQL_CAST(column_name, "VARCHAR(MAX)");
        }

        public virtual string SQL_ToGuid(string column_name)
        {
            return SQL_CAST(column_name, "UNIQUEIDENTIFIER");
        }

        public virtual string SQL_ToDate(string column_name)
        {
            return SQL_CAST(column_name, "DATETIME");
        }

        public virtual string SQL_ToDateShort(string column_name)
        {
            return SQL_CAST(column_name, "DATE");
        }

        public virtual string SQL_ToTime(string column_name)
        {
            return SQL_CAST(column_name, "TIME");
        }
        #endregion

        #region SqlTemplet
        /// <summary>
        /// 获取数据库时间函数
        /// </summary>
        public virtual string SqlDateNow
        {
            get
            {
                return " GETDATE() ";
            }
        }
        /// <summary>
        /// 获取当前时间sql语句
        /// </summary>
        public virtual string FullSqlDateNow
        {
            get
            {
                return "SELECT GETDATE()";
            }
        }


        /// <summary>
        /// 通过连接字符串和表名获取数据库表的信息
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="DbName"></param>
        /// <returns></returns>
        public virtual SqlEntity GetDbTableInfoSQL(IDbHelper dbHelper, string tableName, string DbName)
        {
            var sqle = dbHelper.CreateSqlEntity();
            sqle.Sql = string.Format(GetColumnInfosByTableNameSql, DbName, tableName);
            return sqle;
        }
        /// <summary>
        /// 一般查询语句模板
        /// </summary>
        /// <param name="tbname"></param>
        /// <param name="fieldsstr"></param>
        /// <param name="orderbyStr"></param>
        /// <param name="top"></param>
        /// <returns></returns>
        public virtual string GetSelectSQL(String tbname, string fieldsstr, string orderbyStr, int top = 0)
        {
            string sqlMeta = @"SELECT " + (top > 0 ? " TOP " + top + " " : "") + fieldsstr + " FROM " + tbname + " WHERE {0} " + orderbyStr;
            return sqlMeta;
        }

        /// <summary>
        /// 分页查询语句模板
        /// </summary>
        /// <param name="tbName">表名</param>
        /// <param name="fieldsstr"></param>
        /// <param name="whereStr"></param>
        /// <param name="orderbyStr"></param>
        /// <param name="page">第几页，从1开始</param>
        /// <param name="nums">每页记录数</param>
        /// <returns></returns>
        public virtual string GetSelectPageSQL(string tbName, string fieldsstr, string whereStr, string orderbyStr, int page = 1, int nums = 25)
        {
            if (page < 1) page = 1;
            if (nums < 1) nums = 10;
            var m = (page - 1) * nums + 1;
            var n = page * nums;

            var sqlMeta = "select * from("
                                 + "select " + fieldsstr + $" ,ROW_NUMBER() over({orderbyStr}) as RN  from {tbName} where {whereStr }) as t "
                              + $" where t.RN between {m} and {n}";
            return sqlMeta;
        }
        /// <summary>
        /// MERGE 合并语句模板
        /// </summary>
        /// <param name="_dbType"></param>
        /// <param name="tbname"></param>
        /// <returns></returns>
        public virtual string GetMergeSql(String tbname)
        {
            var sqlMeta = @"MERGE " + tbname + " AS [target]  USING(SELECT 1 AS ____Id_____) AS source ON {0}  WHEN MATCHED THEN {1}  WHEN NOT MATCHED THEN {2};";

            return sqlMeta;
        }

        /// <summary>
        /// 更新语句模板
        /// "UPDATE " + (isMerge ? "" : fromStr) + " SET {0} " + (isMerge ? "" : " WHERE {1} ")
        /// </summary>
        /// <param name="_dbType"></param>
        /// <param name="fromStr"></param>
        /// <param name="isMerge">是参与MERGE</param>
        /// <returns></returns>
        public virtual string GetUpdateSql(String fromStr, bool isMerge = false)
        {
            var sqlMeta = "UPDATE " + (isMerge ? "" : fromStr) + " SET {0} " + (isMerge ? "" : " WHERE {1} ");

            return sqlMeta;
        }
        /// <summary>
        /// 插入语句模板
        /// "INSERT " + (isMerge ? "" : " INTO " + fromStr) + " ({0}) VALUES ({1})"
        /// </summary>
        /// <param name="_dbType"></param>
        /// <param name="fromStr"></param>
        /// <param name="isMerge">是参与MERGE</param>
        /// <returns></returns>
        public virtual string GetInsertSql(String fromStr, bool isMerge = false)
        {
            var sqlMeta = "INSERT " + (isMerge ? "" : " INTO " + fromStr) + " ({0}) VALUES ({1})";

            return sqlMeta;
        }
        /// <summary>
        /// 统计数量语句模板
        /// $"SELECT Count(0) FROM {fromStr} WHERE {0}";
        /// </summary>
        /// <param name="_dbType"></param>
        /// <param name="fromStr"></param>
        /// <param name="fieldName"></param>
        /// <returns></returns>
        public virtual string GetCountSql(String fromStr, string fieldName = "")
        {
            var sqlMeta = "SELECT Count(" + (fieldName.IsNullOrEmpty() ? "0" : fieldName) + ") FROM " + fromStr + " WHERE {0}";

            return sqlMeta;
        }
        /// <summary>
        /// GetInsertSelectSql
        /// INSERT INTO " + insertTable + " ({0}) " + " SELECT {1} FROM " + selectTable + "  WHERE {2} 
        /// </summary>
        /// <param name="insertTable"></param>
        /// <param name="selectTable"></param>
        /// <param name="_dbType"></param>
        /// <returns></returns>
        public virtual string GetInsertSelectSql(String insertTable, String selectTable)
        {
            var sqlMeta = "INSERT INTO " + insertTable + " ({0}) " +
                            " SELECT {1} FROM " + selectTable + "  WHERE {2} ";

            return sqlMeta;
        }
        #endregion

        #region WithNextSequence
        /// <summary>
        /// 下个序列脚本
        /// </summary>
        /// <param name="sequenceName"></param>
        /// <returns></returns>
        public virtual string WithNextSequence(string sequenceName = null)
        {
            if (string.IsNullOrEmpty(sequenceName))
            {
                sequenceName = "default_seq";
            }
            return $"nextval('{sequenceName}')";
        }
        #endregion

        #region DML CodeFirst
        /// <summary>
        /// 获取所有数据库
        /// </summary>
        public virtual string GetDataBaseSql
        {
            get
            {
                return "SELECT Name FROM MASTER.DBO.SYSDATABASES ORDER BY Name";
            }
        }
        /// <summary>
        /// 获取数据库中的所有表
        /// </summary>
        public virtual string GetTableInfoListSql
        {
            get
            {
                return "SELECT name,xtype,(SELECT top 1 value FROM {0}.sys.extended_properties  WHERE {0}.sys.extended_properties.major_id = {0}.dbo.sysobjects.id  AND {0}.sys.extended_properties.minor_id=0) AS [Description] From {0}.dbo.sysobjects WHERE xtype = 'u'  ORDER BY name";
            }
        }
        /// <summary>
        /// 获取数据库中的所有视图
        /// </summary>
        public virtual string GetViewInfoListSql
        {
            get
            {
                return "SELECT name,xtype,(SELECT top 1 value FROM {0}.sys.extended_properties  WHERE {0}.sys.extended_properties.major_id = {0}.dbo.sysobjects.id  AND {0}.sys.extended_properties.minor_id=0) AS [Description] From {0}.dbo.sysobjects WHERE xtype = 'v'  ORDER BY name";
            }
        }

        /// <summary>
        /// 得到一个表的所有列信息
        /// </summary>
        public virtual string GetColumnInfosByTableNameSql
        {
            get
            {
                return @"";
            }
        }
        #endregion

        #region DDL
        #region Scattered
        protected virtual string CreateTableNull
        {
            get
            {
                return "NULL";
            }
        }
        protected virtual string CreateTableNotNull
        {
            get
            {
                return " NOT NULL ";
            }
        }
        protected virtual string CreateTablePirmaryKey
        {
            get
            {
                return "PRIMARY KEY";
            }
        }
        protected virtual string CreateTableIdentity
        {
            get
            {
                return "";
            }
        }
        #endregion
        #region DDL SQL
        protected virtual string IsAnyIndexSql
        {
            get
            {
                return "select count(1) from user_ind_columns where index_name=('{0}')";
            }
        }
        protected virtual string CreateIndexSql
        {
            get
            {
                return "CREATE {3} INDEX Index_{0}_{2} ON {0}({1})";
            }
        }
        protected virtual string AddDefaultValueSql
        {
            get
            {
                return "ALTER TABLE {0} MODIFY({1} DEFAULT '{2}')";
            }
        }
        protected virtual string CreateDataBaseSql
        {
            get
            {
                return "CREATE DATABASE {0}";
            }
        }
        protected virtual string AddPrimaryKeySql
        {
            get
            {
                return "ALTER TABLE {0} ADD PRIMARY KEY({2}) /*{1}*/";
            }
        }
        protected virtual string AddColumnToTableSql
        {
            get
            {
                return "ALTER TABLE {0} ADD ({1} {2}{3} {4} {5} {6})";
            }
        }
        protected virtual string AlterColumnToTableSql
        {
            get
            {
                return "ALTER TABLE {0} modify ({1} {2}{3} {4} {5} {6}) ";
            }
        }
        protected virtual string BackupDataBaseSql
        {
            get
            {
                return @"USE master;BACKUP DATABASE {0} TO disk = '{1}'";
            }
        }
        protected virtual string CreateTableSql
        {
            get
            {
                return "CREATE TABLE {0}(\r\n{1})";
            }
        }
        protected virtual string CreateTableColumn
        {
            get
            {
                return "{0} {1}{2} {3} {4} {5}";
            }
        }
        protected virtual string TruncateTableSql
        {
            get
            {
                return "TRUNCATE TABLE {0}";
            }
        }
        protected virtual string BackupTableSql
        {
            get
            {
                return "create table {1} as select * from {2}  where ROWNUM<={0}";
            }
        }
        protected virtual string DropTableSql
        {
            get
            {
                return "DROP TABLE {0}";
            }
        }
        protected virtual string DropColumnToTableSql
        {
            get
            {
                return "ALTER TABLE {0} DROP COLUMN {1}";
            }
        }
        protected virtual string DropConstraintSql
        {
            get
            {
                return "ALTER TABLE {0} DROP CONSTRAINT  {1}";
            }
        }
        protected virtual string RenameColumnSql
        {
            get
            {
                return "ALTER TABLE {0} rename   column  {1} to {2}";
            }
        }
        protected virtual string AddColumnRemarkSql
        {
            get
            {
                return "comment on column {1}.{0} is '{2}'";
            }
        }

        protected virtual string DeleteColumnRemarkSql
        {
            get
            {
                return "comment on column {1}.{0} is ''";
            }
        }

        protected virtual string IsAnyColumnRemarkSql
        {
            get
            {
                return "select * from user_col_comments where Table_Name='{1}' AND COLUMN_NAME='{0}' order by column_name";
            }
        }

        protected virtual string AddTableRemarkSql
        {
            get
            {
                return "comment on table {0}  is  '{1}'";
            }
        }

        protected virtual string DeleteTableRemarkSql
        {
            get
            {
                return "comment on table {0}  is  ''";
            }
        }

        protected virtual string IsAnyTableRemarkSql
        {
            get
            {
                return "select * from user_tab_comments where Table_Name='{0}'order by Table_Name";
            }
        }

        protected virtual string RenameTableSql
        {
            get
            {
                return "alter table {0} rename to {1}";
            }
        }
        #endregion
        #region protected
        protected virtual List<T> GetListOrCache<T>(string cacheKey, string sql) where T : class
        {
            var sqle = this.dbHelper.CreateSqlEntity(sql);
            return Funs.CacheGet(cacheKey,
             () =>
             {
                 var result = this.dbHelper.Query<T>(sqle);
                 return result;
             });
        }
        protected virtual string GetCreateTableSql(string tableName, List<DbColumnInfo> columns)
        {
            List<string> columnArray = new List<string>();
            Check.Exception(columns.IsNullOrEmpty(), "No columns found ");
            foreach (var item in columns)
            {
                string columnName = this.GetTranslationTableName(item.Name);
                string dataType = item.Type;
                string dataSize = GetSize(item);
                string nullType = item.IsNullable ? this.CreateTableNull : CreateTableNotNull;
                string primaryKey = null;
                string identity = item.IsIdentity ? this.CreateTableIdentity : null;
                string addItem = string.Format(this.CreateTableColumn, columnName, dataType, dataSize, nullType, primaryKey, identity);
                columnArray.Add(addItem);
            }
            string tableString = string.Format(this.CreateTableSql, this.GetTranslationTableName(tableName), string.Join(",\r\n", columnArray));
            return tableString;
        }
        protected virtual string GetAddColumnSql(string tableName, DbColumnInfo columnInfo)
        {
            string columnName = this.GetTranslationColumnName(columnInfo.Name);
            tableName = this.GetTranslationTableName(tableName);
            string dataType = columnInfo.Type;
            string dataSize = GetSize(columnInfo);
            string nullType = columnInfo.IsNullable ? this.CreateTableNull : CreateTableNotNull;
            string primaryKey = null;
            string identity = null;
            string result = string.Format(this.AddColumnToTableSql, tableName, columnName, dataType, dataSize, nullType, primaryKey, identity);
            return result;
        }
        protected virtual string GetUpdateColumnSql(string tableName, DbColumnInfo columnInfo)
        {
            string columnName = this.GetTranslationColumnName(columnInfo.Name);
            tableName = this.GetTranslationTableName(tableName);
            string dataSize = GetSize(columnInfo);
            string dataType = columnInfo.Type;
            string nullType = columnInfo.IsNullable ? this.CreateTableNull : CreateTableNotNull;
            string primaryKey = null;
            string identity = null;
            string result = string.Format(this.AlterColumnToTableSql, tableName, columnName, dataType, dataSize, nullType, primaryKey, identity);
            return result;
        }
        protected virtual string GetCacheKey(string cacheKey)
        {
            return this.DBType + "." + Connection.Database + "." + cacheKey;
        }
        protected virtual string GetSize(DbColumnInfo item)
        {
            string dataSize = null;
            var isMax = item.MaxLength > 4000 || item.MaxLength == -1;
            if (isMax)
            {
                dataSize = item.MaxLength > 0 ? string.Format("({0})", "max") : null;
            }
            else if (item.MaxLength == 0 && item.DecimalDigits > 0)
            {
                item.MaxLength = 10;
                dataSize = string.Format("({0},{1})", item.MaxLength, item.DecimalDigits);
            }
            else if (item.MaxLength > 0 && item.DecimalDigits == 0)
            {
                dataSize = item.MaxLength > 0 ? string.Format("({0})", item.MaxLength) : null;
            }
            else if (item.MaxLength > 0 && item.DecimalDigits > 0)
            {
                dataSize = item.MaxLength > 0 ? string.Format("({0},{1})", item.MaxLength, item.DecimalDigits) : null;
            }
            return dataSize;
        }
        #endregion
        #region Check
        protected virtual string CheckSystemTablePermissionsSql
        {
            get
            {
                return "select top 1 id from sysobjects";
            }
        }
        public virtual bool IsAnyTable(string tableName, bool isCache = true)
        {
            Check.Exception(string.IsNullOrEmpty(tableName), "IsAnyTable tableName is not null");
            tableName = this.GetNoTranslationColumnName(tableName);
            var tables = GetTableInfoList(Connection.Database, isCache);
            if (tables == null) return false;
            else return tables.Any(it => it.TableName.Equals(tableName, StringComparison.CurrentCultureIgnoreCase));
        }
        public virtual bool IsAnyColumn(string tableName, string columnName, bool isCache = true)
        {
            columnName = this.GetNoTranslationColumnName(columnName);
            tableName = this.GetNoTranslationColumnName(tableName);
            var isAny = IsAnyTable(tableName, isCache);
            Check.Exception(!isAny, string.Format("Table {0} does not exist", tableName));
            var columns = GetColumnInfosByTableName(tableName, Connection.Database, isCache);
            if (columns.IsNullOrEmpty()) return false;
            return columns.Any(it => it.Name.Equals(columnName, StringComparison.CurrentCultureIgnoreCase));
        }
        public virtual bool IsPrimaryKey(string tableName, string columnName)
        {
            columnName = this.GetNoTranslationColumnName(columnName);
            var isAny = IsAnyTable(tableName);
            Check.Exception(!isAny, string.Format("Table {0} does not exist", tableName));
            var columns = GetColumnInfosByTableName(tableName, Connection.Database);
            if (columns.IsNullOrEmpty()) return false;
            var result = columns.Any(it => it.IsPrimaryKey == true && it.Name.Equals(columnName, StringComparison.CurrentCultureIgnoreCase));
            return result;
        }
        public virtual bool IsIdentity(string tableName, string columnName)
        {
            columnName = this.GetNoTranslationColumnName(columnName);
            var isAny = IsAnyTable(tableName);
            Check.Exception(!isAny, string.Format("Table {0} does not exist", tableName));
            var columns = GetColumnInfosByTableName(tableName, Connection.Database);
            if (columns.IsNullOrEmpty()) return false;
            return columns.Any(it => it.IsIdentity = true && it.Name.Equals(columnName, StringComparison.CurrentCultureIgnoreCase));
        }
        public virtual bool IsAnyConstraint(string constraintName)
        {
            var sqle = this.dbHelper.CreateSqlEntity("select  object_id('" + constraintName + "')");

            return this.dbHelper.GetSingle<int>(sqle) > 0;
        }
        public virtual bool IsAnySystemTablePermissions()
        {
            CheckConnection();
            string sql = this.CheckSystemTablePermissionsSql;
            try
            {
                var sqle = this.dbHelper.CreateSqlEntity(sql);
                this.dbHelper.Execute(sqle);
                return true;
            }
            catch
            {
                return false;
            }
        }
        #endregion

        public virtual List<string> GetDataBaseList(IDbHelper db = null)
        {
            if (db == null) db = dbHelper;
            var sqle = db.CreateSqlEntity(GetDataBaseSql);
            return db.Query<string>(sqle);
        }
        /// <summary>
        ///by current connection string
        /// </summary>
        /// <param name="databaseDirectory"></param>
        /// <returns></returns>
        public virtual bool CreateDatabase(string databaseDirectory = null)
        {
            var seChar = System.IO.Path.DirectorySeparatorChar.ToString();
            if (databaseDirectory.IsNotNullOrEmpty())
            {
                databaseDirectory = databaseDirectory.TrimEnd('\\').TrimEnd('/');
            }
            var databaseName = Connection.Database;
            return CreateDatabase(databaseName, databaseDirectory);
        }
        /// <summary>
        /// by databaseName
        /// </summary>
        /// <param name="databaseName"></param>
        /// <param name="databaseDirectory"></param>
        /// <returns></returns>
        public virtual bool CreateDatabase(string databaseName, string databaseDirectory = null)
        {
            var sqle = this.dbHelper.CreateSqlEntity(string.Format(CreateDataBaseSql, databaseName, databaseDirectory));
            this.dbHelper.Execute(sqle);
            return true;
        }

        public virtual bool AddPrimaryKey(string tableName, string columnName)
        {
            tableName = this.GetTranslationTableName(tableName);
            columnName = this.GetTranslationTableName(columnName);
            string sql = string.Format(this.AddPrimaryKeySql, tableName, string.Format("PK_{0}_{1}", this.GetNoTranslationColumnName(tableName), this.GetNoTranslationColumnName(columnName)), columnName);
            var sqle = this.dbHelper.CreateSqlEntity(sql);
            this.dbHelper.Execute(sqle);
            return true;
        }

        public bool AddPrimaryKeys(string tableName, string[] columnNames)
        {
            tableName = this.GetTranslationTableName(tableName);
            var columnName = string.Join(",", columnNames);
            var pkName = string.Format("PK_{0}_{1}", this.GetNoTranslationColumnName(tableName), columnName.Replace(",", "_"));
            string sql = string.Format(this.AddPrimaryKeySql, tableName, pkName, columnName);
            var sqle = this.dbHelper.CreateSqlEntity(sql);
            this.dbHelper.Execute(sqle);
            return true;
        }
        public virtual bool AddColumn(string tableName, DbColumnInfo columnInfo)
        {
            tableName = this.GetTranslationTableName(tableName);
            string sql = GetAddColumnSql(tableName, columnInfo);
            var sqle = this.dbHelper.CreateSqlEntity(sql);
            this.dbHelper.Execute(sqle);
            return true;
        }
        public virtual bool UpdateColumn(string tableName, DbColumnInfo column)
        {
            tableName = this.GetTranslationTableName(tableName);
            string sql = GetUpdateColumnSql(tableName, column);
            var sqle = this.dbHelper.CreateSqlEntity(sql);
            this.dbHelper.Execute(sqle);
            return true;
        }
        public virtual bool CreateTable(string tableName, List<DbColumnInfo> columns, bool isCreatePrimaryKey = true)
        {
            tableName = this.GetTranslationTableName(tableName);
            string sql = GetCreateTableSql(tableName, columns);
            var sqle = this.dbHelper.CreateSqlEntity(sql);
            this.dbHelper.Execute(sqle);
            if (isCreatePrimaryKey)
            {
                var pkColumns = columns.Where(it => it.IsPrimaryKey).ToList();
                if (pkColumns.Count > 1)
                {
                    AddPrimaryKeys(tableName, pkColumns.Select(it => it.Name).ToArray());
                }
                else
                {
                    foreach (var item in pkColumns)
                    {
                        AddPrimaryKey(tableName, item.Name);
                    }
                }
            }
            return true;
        }
        public virtual bool DropTable(string tableName)
        {
            tableName = this.GetTranslationTableName(tableName);
            var sqle = this.dbHelper.CreateSqlEntity(string.Format(this.DropTableSql, tableName));
            this.dbHelper.Execute(sqle);
            return true;
        }

        public virtual bool TruncateTable<T>()
        {
            var tb = this.dbHelper.GetDbTableInfo(typeof(T));
            return this.TruncateTable(tb.TableName);
        }
        public virtual bool DropColumn(string tableName, string columnName)
        {
            columnName = this.GetTranslationColumnName(columnName);
            tableName = this.GetTranslationTableName(tableName);
            var sqle = this.dbHelper.CreateSqlEntity(string.Format(this.DropColumnToTableSql, tableName, columnName));
            this.dbHelper.Execute(sqle);
            return true;
        }
        public virtual bool DropConstraint(string tableName, string constraintName)
        {
            tableName = this.GetTranslationTableName(tableName);
            string sql = string.Format(this.DropConstraintSql, tableName, constraintName);
            var sqle = this.dbHelper.CreateSqlEntity(sql);
            this.dbHelper.Execute(sqle);
            return true;
        }
        public virtual bool TruncateTable(string tableName)
        {
            tableName = this.GetTranslationTableName(tableName);
            var sqle = this.dbHelper.CreateSqlEntity(string.Format(this.TruncateTableSql, tableName));
            this.dbHelper.Execute(sqle);
            return true;
        }
        public virtual bool BackupDataBase(string databaseName, string fullFileName)
        {
            var directory = FileHelper.GetDirectoryFromFilePath(fullFileName);
            if (!FileHelper.IsExistDirectory(directory))
            {
                FileHelper.CreateDirectory(directory);
            }
            var sqle = this.dbHelper.CreateSqlEntity(string.Format(this.BackupDataBaseSql, databaseName, fullFileName));
            this.dbHelper.Execute(sqle);
            return true;
        }
        public virtual bool BackupTable(string oldTableName, string newTableName, int maxBackupDataRows = int.MaxValue)
        {
            oldTableName = this.GetTranslationTableName(oldTableName);
            newTableName = this.GetTranslationTableName(newTableName);
            string sql = string.Format(this.BackupTableSql, maxBackupDataRows, newTableName, oldTableName);
            var sqle = this.dbHelper.CreateSqlEntity(sql);
            this.dbHelper.Execute(sqle);
            return true;
        }
        public virtual bool RenameColumn(string tableName, string oldColumnName, string newColumnName)
        {
            tableName = this.GetTranslationTableName(tableName);
            oldColumnName = this.GetTranslationColumnName(oldColumnName);
            newColumnName = this.GetTranslationColumnName(newColumnName);
            string sql = string.Format(this.RenameColumnSql, tableName, oldColumnName, newColumnName);
            var sqle = this.dbHelper.CreateSqlEntity(sql);
            this.dbHelper.Execute(sqle);
            return true;
        }
        public virtual bool AddColumnRemark(string columnName, string tableName, string description)
        {
            string sql = string.Format(this.AddColumnRemarkSql, columnName, tableName, description);
            var sqle = this.dbHelper.CreateSqlEntity(sql);
            this.dbHelper.Execute(sqle);
            return true;
        }
        public virtual bool DeleteColumnRemark(string columnName, string tableName)
        {
            string sql = string.Format(this.DeleteColumnRemarkSql, columnName, tableName);
            var sqle = this.dbHelper.CreateSqlEntity(sql);
            this.dbHelper.Execute(sqle);
            return true;
        }
        public virtual bool IsAnyColumnRemark(string columnName, string tableName)
        {
            string sql = string.Format(this.IsAnyColumnRemarkSql, columnName, tableName);
            var sqle = this.dbHelper.CreateSqlEntity(sql);
            var dt = this.dbHelper.QueryDataTable(sqle);
            return dt.Rows != null && dt.Rows.Count > 0;
        }
        public virtual bool AddTableRemark(string tableName, string description)
        {
            string sql = string.Format(this.AddTableRemarkSql, tableName, description);
            var sqle = this.dbHelper.CreateSqlEntity(sql);
            this.dbHelper.Execute(sqle);
            return true;
        }
        public virtual bool DeleteTableRemark(string tableName)
        {
            string sql = string.Format(this.DeleteTableRemarkSql, tableName);
            var sqle = this.dbHelper.CreateSqlEntity(sql);
            this.dbHelper.Execute(sqle);
            return true;
        }
        public virtual bool IsAnyTableRemark(string tableName)
        {
            string sql = string.Format(this.IsAnyTableRemarkSql, tableName);
            var sqle = this.dbHelper.CreateSqlEntity(sql);
            var dt = this.dbHelper.QueryDataTable(sqle);
            return dt.Rows != null && dt.Rows.Count > 0;
        }
        public virtual bool AddDefaultValue(string tableName, string columnName, string defaultValue)
        {
            if (defaultValue == "''")
            {
                defaultValue = "";
            }
            string sql = string.Format(AddDefaultValueSql, tableName, columnName, defaultValue);
            var sqle = this.dbHelper.CreateSqlEntity(sql);
            this.dbHelper.Execute(sqle);
            return true;
        }
        public virtual bool CreateIndex(string tableName, string[] columnNames, bool isUnique = false)
        {
            string sql = string.Format(CreateIndexSql, tableName, string.Join(",", columnNames), string.Join("_", columnNames) + "IX_", isUnique ? "UNIQUE" : "");
            var sqle = this.dbHelper.CreateSqlEntity(sql);
            this.dbHelper.Execute(sqle);
            return true;
        }
        public virtual bool CreateUniqueIndex(string tableName, string[] columnNames)
        {
            string sql = string.Format(CreateIndexSql, tableName, string.Join(",", columnNames), string.Join("_", columnNames) + "IX" + "_Unique", "UNIQUE");
            var sqle = this.dbHelper.CreateSqlEntity(sql);
            this.dbHelper.Execute(sqle);
            return true;
        }
        public virtual bool IsAnyIndex(string indexName)
        {
            string sql = string.Format(this.IsAnyIndexSql, indexName);
            var sqle = this.dbHelper.CreateSqlEntity(sql);
            return this.dbHelper.GetSingle<int>(sqle) > 0;
        }
        public virtual bool AddRemark(DbTableInfo entity)
        {
            var columns = entity.Columns.Where(it => it.IsIgnore == false).ToList();
            foreach (var item in columns)
            {
                if (item.Description != null)
                {
                    //column remak
                    if (IsAnyColumnRemark(item.Name, entity.TableName))
                    {
                        DeleteColumnRemark(item.Name, entity.TableName);
                        AddColumnRemark(item.Name, entity.TableName, item.Description);
                    }
                    else
                    {
                        AddColumnRemark(item.Name, item.Name, item.Description);
                    }
                }
            }

            //table remak
            if (entity.Description != null)
            {
                if (IsAnyTableRemark(entity.TableName))
                {
                    DeleteTableRemark(entity.TableName);
                    AddTableRemark(entity.TableName, entity.Description);
                }
                else
                {
                    AddTableRemark(entity.TableName, entity.Description);
                }
            }
            return true;
        }

        public virtual void AddIndex(DbTableInfo entityInfo)
        {
            var columns = entityInfo.Columns.Where(it => it.IsIgnore == false).ToList();
            var indexColumns = columns.Where(it => it.IndexGroupNameList.HasValue()).ToList();
            if (indexColumns.HasValue())
            {
                var groups = indexColumns.SelectMany(it => it.IndexGroupNameList).GroupBy(it => it).Select(it => it.Key).ToList();
                foreach (var item in groups)
                {
                    var columnNames = indexColumns.Where(it => it.IndexGroupNameList.Any(i => i.Equals(item, StringComparison.CurrentCultureIgnoreCase))).Select(it => it.Name).ToArray();
                    var indexName = string.Format("Index_{0}_{1}IX", string.Join("_", columnNames));
                    if (!IsAnyIndex(indexName))
                    {
                        CreateIndex(entityInfo.TableName, columnNames);
                    }
                }
            }


            var uIndexColumns = columns.Where(it => it.UIndexGroupNameList.HasValue()).ToList();
            if (uIndexColumns.HasValue())
            {
                var groups = uIndexColumns.SelectMany(it => it.UIndexGroupNameList).GroupBy(it => it).Select(it => it.Key).ToList();
                foreach (var item in groups)
                {
                    var columnNames = uIndexColumns.Where(it => it.UIndexGroupNameList.Any(i => i.Equals(item, StringComparison.CurrentCultureIgnoreCase))).Select(it => it.Name).ToArray();
                    var indexName = string.Format("Index_{0}_{1}_UniqueIX", string.Join("_", columnNames));
                    if (!IsAnyIndex(indexName))
                    {
                        CreateUniqueIndex(entityInfo.TableName, columnNames);
                    }
                }
            }
        }

        protected virtual bool IsAnyDefaultValue(string tableName, string columnName, List<DbColumnInfo> columns)
        {
            var defaultValue = columns.Where(it => it.Name.Equals(columnName, StringComparison.CurrentCultureIgnoreCase)).First().DefaultValue;
            return defaultValue.HasValue();
        }

        public virtual bool IsAnyDefaultValue(string tableName, string columnName)
        {
            return IsAnyDefaultValue(tableName, columnName, this.GetColumnInfosByTableName(tableName, Connection.Database, false));
        }

        public virtual void AddDefaultValue(DbTableInfo entityInfo)
        {
            var dbColumns = this.GetColumnInfosByTableName(entityInfo.TableName, Connection.Database, false);
            var columns = entityInfo.Columns.Where(it => it.IsIgnore == false).ToList();
            foreach (var item in columns)
            {
                if (item.DefaultValue.HasValue())
                {
                    if (!IsAnyDefaultValue(entityInfo.TableName, item.Name, dbColumns))
                    {
                        this.AddDefaultValue(entityInfo.TableName, item.Name, item.DefaultValue);
                    }
                }
            }
        }

        public virtual bool RenameTable(string oldTableName, string newTableName)
        {
            string sql = string.Format(this.RenameTableSql, oldTableName, newTableName);
            var sqle = this.dbHelper.CreateSqlEntity(sql);
            this.dbHelper.Execute(sqle);
            return true;
        }
        #endregion

        #region BulkCopyData
        /// <summary>  
        /// 批量插入功能  
        /// </summary>  
        public virtual bool BulkCopyData(DataTable table, string destinationTableName = null, int? bulkCopyTimeout = null)
        {
            if (table == null || table.Rows.Count == 0) return false;
            if (string.IsNullOrEmpty(destinationTableName))
                destinationTableName = table.TableName;
            var cnn = (dbHelper as DbHelper).GetDbConnection();
            try
            {
                using (var bulkCopy = BulkCopy.Create(cnn))
                {
                    bulkCopy.EnableStreaming = true;
                    bulkCopy.BatchSize = table.Rows.Count;
                    bulkCopy.DestinationTableName = destinationTableName;
                    if (bulkCopyTimeout != null)
                        bulkCopy.BulkCopyTimeout = bulkCopyTimeout.Value;

                    bulkCopy.WriteToServer(table);
                }
            }
            catch (Exception e)
            {
                (dbHelper as DbHelper).Rollback();
                (dbHelper as DbHelper).CloseConnection(cnn);
                throw e;
            }
            return true;
        }
        /// <summary>  
        ///将DataTable转换为标准的CSV  
        /// </summary>  
        /// <param name="table">数据表</param>  
        /// <returns>返回标准的CSV</returns>  
        protected string DataTableToCsv(DataTable table)
        {
            //以半角逗号（即,）作分隔符，列为空也要表达其存在。  
            //列内容如存在半角逗号（即,）则用半角引号（即""）将该字段值包含起来。  
            //列内容如存在半角引号（即"）则应替换成半角双引号（""）转义，并用半角引号（即""）将该字段值包含起来。  
            StringBuilder sb = new StringBuilder();
            DataColumn colum;
            foreach (DataRow row in table.Rows)
            {
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    colum = table.Columns[i];
                    if (i != 0) sb.Append(",");
                    if (colum.DataType == typeof(string) && row[colum].ToString().Contains(","))
                    {
                        sb.Append("\"" + row[colum].ToString().Replace("\"", "\"\"") + "\"");
                    }
                    else sb.Append(row[colum].ToString());
                }
                sb.AppendLine();
            }
            return sb.ToString();
        }
        #endregion

        #region ExecutePro
        protected static Dictionary<string, Dictionary<string, CNDbParameter>> dicDbParameter = new Dictionary<string, Dictionary<string, CNDbParameter>>(StringComparer.OrdinalIgnoreCase);
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public virtual bool ExecutePro(SqlEntity sql, Dictionary<string, string> paras)
        {
            if (sql.Sql.IsNullOrEmpty_()) return false;

            var ret = dbHelper.Execute(GetSqlEntityPro(sql, paras));
            return ret;
        }
        /// <summary>
        /// 生成存储过程参数
        /// </summary>
        /// <param name="_StoredProcName"></param>
        /// <param name="obj"></param>
        /// <returns></returns>
        public virtual SqlEntity GetSqlEntityPro(SqlEntity sql, object obj)
        {
            var paras = obj.ToDictionary();
            return GetSqlEntityPro(sql, paras);
        }
        /// <summary>
        /// 初始化存储过程参数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public virtual SqlEntity GetSqlEntityPro(SqlEntity sql, Dictionary<string, object> paras)
        {
            sql.CommandType = CommandType.StoredProcedure;
            if (paras?.Count > 0)
            {
                foreach (var dbp in paras)
                {
                    sql.AddParameter(dbp.Key, dbp.Value);
                }
            }
            return sql;
        }
        #endregion
    }
}
